Create database Fashion

CREATE TABLE Languages (
LanguageID VARCHAR(10) NOT NULL PRIMARY KEY,
LanguageName VARCHAR(255) NOT NULL
) 
CREATE TABLE Users (
UserID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Username VARCHAR( 255 ) NOT NULL ,
Password VARCHAR( 30 ) NOT NULL ,
RoleID INT NOT NULL,
Avatar VARCHAR(255) NULL,
FirstName VARCHAR( 50 ) NOT NULL ,
LastName VARCHAR( 50 ) NOT NULL ,
AddressLine1 VARCHAR( 255 ) NOT NULL ,
AddressLine2 VARCHAR( 255 ) NOT NULL ,
City VARCHAR( 25 ) NOT NULL ,
PostalCode INT NOT NULL ,
Country VARCHAR( 25 ) NOT NULL ,
Phone VARCHAR( 25 ) NOT NULL ,
Email VARCHAR( 100 ) NOT NULL
)  

CREATE TABLE Product (
ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
CategoryID INT NOT NULL,
ImageList VARCHAR( 255 ) NOT NULL ,
Price FLOAT NOT NULL ,
DateAdded DATETIME NOT NULL ,
Rating FLOAT NOT NULL ,
Likes INT NOT NULL,
BrandID INT NOT NULL ,
Sex VARCHAR( 10 ) NOT NULL
)  
CREATE TABLE  Product_translation(
ProductID INT NOT NULL,
LanguageID VARCHAR(10) NOT NULL ,
TranslationText VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( ProductID , LanguageID )
)  

CREATE TABLE  Brand (
BrandID INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
BrandName VARCHAR( 50 ) NOT NULL
)  
CREATE TABLE Rating (
ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Rating FLOAT NOT NULL ,
)  
CREATE TABLE Size (
SizeID INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
Name INT NOT NULL ,
)  
CREATE TABLE Color (
ColorID INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
Name VARCHAR( 25 ) NOT NULL
)  
CREATE TABLE Color_translation (
ColorID INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
LanguageID VARCHAR(10) NOT NULL ,
TranslationText VARCHAR(50) NOT NULL 
)  
CREATE TABLE ProductDetail (
ProductDetailID INT NOT NULL IDENTITY(1,1) ,
ProductID INT NOT NULL ,
ColorID INT NOT NULL ,
SizeID INT NOT NULL ,
ImageList VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( ProductDetailID , ProductID , ColorID , SizeID )
)  
CREATE TABLE  Coupon (
CouponID INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
ProductID INT NOT NULL ,
Value FLOAT NOT NULL ,
ExpiredDate DATETIME NOT NULL ,
StartDate DATETIME NOT NULL
)  

CREATE TABLE Category (
CategoryID INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
Name VARCHAR( 100 ) NOT NULL 
)  
CREATE TABLE  Category_translation (
CategoryID INT NOT NULL IDENTITY(1,1) ,	
LanguageID VARCHAR(10) NOT NULL ,
TranslationText VARCHAR( 255 ) NOT NULL,
PRIMARY KEY(CategoryID, LanguageID)
)  

CREATE TABLE Orders (
OrderID INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
UserID INT NOT NULL,
TotalPrice FLOAT NOT NULL ,
OrderDate DATETIME NOT NULL ,
ShipDate DATETIME NOT NULL ,
CurrentStatus VARCHAR( 125 ) NOT NULL ,
AddressLine1 VARCHAR( 255 ) NOT NULL ,
AddressLine2 VARCHAR( 255 ) NULL ,
City VARCHAR( 255 ) NOT NULL ,
Postalcode INT NOT NULL ,
Country VARCHAR( 50 ) NOT NULL
)  
CREATE TABLE OrderDetail (
OrderID INT NOT NULL ,
ProductID INT NOT NULL ,
Quantity INT NOT NULL ,
Price INT NOT NULL ,
SizeID INT NOT NULL ,
ColorID INT NOT NULL ,
CouponID INT NULL 
)  
CREATE TABLE Exchange (
CurrencyID VARCHAR( 5 ) NOT NULL PRIMARY KEY,
Date DATETIME NOT NULL ,
CurrencyRate FLOAT NOT NULL 
) 

CREATE TABLE Roles(
RoleID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
RoleName VARCHAR(25) NOT NULL
) 

ALTER TABLE Users
ADD CONSTRAINT fk_roles_pid FOREIGN KEY(RoleID)REFERENCES Roles(RoleID)

ALTER TABLE Product
ADD CONSTRAINT fk_category_pid FOREIGN KEY(CategoryID) REFERENCES Category(CategoryID)

ALTER TABLE Product
ADD CONSTRAINT fk_brand_pid FOREIGN KEY(BrandID) REFERENCES Brand(BrandID)

ALTER TABLE Product_translation
ADD CONSTRAINT fk_languageProduct_pid FOREIGN KEY(LanguageID) REFERENCES Languages(LanguageID)

ALTER TABLE Color_translation
ADD CONSTRAINT fk_languageColor_pid FOREIGN KEY(LanguageID) REFERENCES Languages(LanguageID)

ALTER TABLE Rating
ADD CONSTRAINT fk_productRating_pid FOREIGN KEY(ProductID) REFERENCES Product(ProductID)

ALTER TABLE ProductDetail
ADD CONSTRAINT fk_productDetail_pid FOREIGN KEY(ProductID) REFERENCES Product(ProductID)

ALTER TABLE ProductDetail
ADD CONSTRAINT fk_colorProduct_pid FOREIGN KEY(ColorID) REFERENCES Color(ColorID)

ALTER TABLE ProductDetail
ADD CONSTRAINT fk_sizeProduct_pid FOREIGN KEY(SizeID) REFERENCES Size(SizeID)

ALTER TABLE Coupon
ADD CONSTRAINT fk_productCoupon_pid FOREIGN KEY(ProductID) REFERENCES Product(ProductID)

ALTER TABLE Category_translation
ADD CONSTRAINT fk_languageCategory_pid FOREIGN KEY(LanguageID) REFERENCES Languages(LanguageID)

ALTER TABLE Orders
ADD CONSTRAINT fk_userOrder_pid FOREIGN KEY(UserID) REFERENCES Users(UserID)

ALTER TABLE OrderDetail
ADD CONSTRAINT fk_orderDetail_pid FOREIGN KEY(OrderID) REFERENCES Orders(OrderID)

ALTER TABLE OrderDetail
ADD CONSTRAINT fk_productOrder_pid FOREIGN KEY(ProductID) REFERENCES Product(ProductID)

ALTER TABLE OrderDetail
ADD CONSTRAINT fk_sizeOrder_pid FOREIGN KEY(SizeID) REFERENCES Size(SizeID)

ALTER TABLE OrderDetail
ADD CONSTRAINT fk_colorOrder_pid FOREIGN KEY(ColorID) REFERENCES Color(ColorID)

ALTER TABLE OrderDetail
ADD CONSTRAINT fk_couponOrder_pid FOREIGN KEY(CouponID) REFERENCES Coupon(CouponID)



